﻿using CommonLib;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace YouErYuanBLL
{
    public class loginReportBLL
    {
        /// <summary>
        /// 查询登陆统计情况
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public DataTable loginLogs(int pageIndex, int pageSize, string startTime, string endTime, string vision, out int total)
        {
            //成长档案开始时间
            string growStart = startTime == "" ? "" : " and creatTime>='" + startTime + "' ";
            //成长档案结束时间
            string growEnd = endTime == "" ? "" : " and creatTime<='" + endTime + " 23:59' ";
            //登陆开始时间
            string logStartTime = startTime == "" ? "" : " and loginTime>='" + startTime + "' ";
            //登陆结束时间
            string logEndTime = endTime == "" ? "" : " and loginTime<='" + endTime + " 23:59" + "' ";

            string vLogType = "v_loginTypes";
            string vZanType = "v_zan_jiazhang";
            string vPinType = "v_pinglun_jiazhang";
            string vGrowType = "v_grow";
            //园丁或家长
            if (vision == "2")
            {
                vLogType = "v_loginType_teacher";
                vZanType = "v_zan_teacher";
                vPinType = "v_PingLun_teacher";
                vGrowType = "v_grow_teacher";
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("(select schoolid,schoolName");
            //登陆总次数
            sql.Append(",(select COUNT(1) from " + vLogType + " where schoolid=a.schoolid " + logStartTime + logEndTime + ")as loginCount");
            //今天登陆次数
            sql.Append(",(select COUNT(1) from " + vLogType + " where DATEDIFF(dd,creatTime,GETDATE())=0 and schoolId=a.schoolId) as DayCount");
            //总发布
            sql.Append(",(select COUNT(1) from "+ vGrowType + " where schoolid=a.schoolid " + growStart + growEnd + ")as growCount");
            //今天发布
            sql.Append(",(select COUNT(1) from "+ vGrowType + " where DATEDIFF(dd,creatTime,GETDATE())=0 and schoolId=a.schoolId) as growDay");
            //点赞总次
            sql.Append(",(select COUNT(1) from " + vZanType + " where schoolid=a.schoolid ) as zanCount");
            //评论总次数
            sql.Append(",(select COUNT(1) from " + vPinType + " where schoolid=a.schoolid " + growStart + growEnd + ")as pingCount");
            sql.Append(" from T_School a ) as s");
            DataTable dt = SQLHepler.GetPaging(" loginCount desc", sql.ToString(), "", pageIndex, pageSize, out total);
            return dt;
        }

        /// <summary>
        /// 每个幼儿园的登录情况
        /// </summary>
        /// <param name="schoolID"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public DataTable loginInfo(string schoolID, string startTime, string endTime, string vision)
        {
            //成长档案开始时间
            string growStart = startTime == "" ? "" : " and creatTime>=@startTime ";
            //成长档案结束时间
            string growEnd = endTime == "" ? "" : " and creatTime<=@endTime ";
            //登陆开始时间
            string logStartTime = startTime == "" ? "" : " and loginTime>=@startTime ";
            //登陆结束时间
            string logEndTime = endTime == "" ? "" : " and loginTime<=@endTime ";

            string vLogType = "v_loginTypes";
            string vZanType = "v_zan_jiazhang";
            string vPinType = "v_pinglun_jiazhang";
            string vGrowType = "v_grow";
            string es = "stuName+'的'+guanxi as uname,className";
            string eu = "a.stuName,a.guanxi,a.className";

            //园丁或家长
            if (vision == "2")
            {
                vLogType = "v_loginType_teacher";
                vZanType = "v_zan_teacher";
                vPinType = "v_PingLun_teacher";
                vGrowType = "v_grow_teacher";

                es = "uname";
                eu = "uname";
            }
            StringBuilder sql = new StringBuilder();
            sql.Append("select " + es + ",phone,COUNT(uid)as loginCount,MAX(loginTime)as times");
            //总发布
            sql.Append(",(select COUNT(1) from "+ vGrowType + " where uid=a.uid " + growStart + growEnd + ")as growCount");
            //今天发布
            sql.Append(",(select COUNT(1) from "+ vGrowType + " where DATEDIFF(dd,creatTime,GETDATE())=0 and uid=a.uid) as growDay");
            //点赞总次
            sql.Append(",(select COUNT(1) from " + vZanType + " where uid=a.uid ) as zanCount");
            //评论总次数
            sql.Append(",(select COUNT(1) from " + vPinType + " where uid=a.uid " + growStart + growEnd + ")as pingCount ");
            sql.Append("from " + vLogType + " a where a.schoolid=@schoolid group by a.phone," + eu + ",a.uid order by loginCount desc");
            SqlParameter[] par = new SqlParameter[] {
                new SqlParameter("@startTime",startTime),
                new SqlParameter("@endTime",endTime+" 23:59"),
                new SqlParameter("@schoolid",schoolID)
            };
            return SQLHepler.GetDataTable(sql.ToString(), par);
        }
    }
}
